--Test Data
use logistics
go
--
--delete from trip_package
--delete from trip_route
--delete from trip
--delete from vehicle
--delete from warehouse
--
--delete from [route]
--delete from package
--delete from delivery
--delete from customer
--delete from account
--delete from branch
--delete from city

--City
insert into city(city_name) values ('Ho Chi Minh')
insert into city(city_name) values ('Vung Tau')
insert into city(city_name) values ('Da Lat')
insert into city(city_name) values ('Dong Nai')
insert into city(city_name) values ('Can Tho')
insert into city(city_name) values ('Da Nang')

select * from city
go

delete city where city_name='Nha Trang'

--Branch 
insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	1,'HCM 01','7 Nam Quoc Cang Str.','hcm01@takk.com','123112233',
	null, null, 0.05, 10, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	1, 'HCM 02', '590 CMT8 Str.', 'hcm02@takk.com', '22612626',
	null, null, 0.05, 5, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	2, 'VungTau 01', '88 Cong Hoa Str.', 'vt01@takk.com', '12512368',
	null, null, 0.08, 10, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	3, 'DaLat 01', '91 Vo Thi Sau Str.', 'dalat01@takk.com', '36612602',
	null, null, 0.08, 5, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	4, 'DongNai 01', '10A Phan Dang Luu Str.', 'dongnai01@takk.com', '32512522',
	null, null, 0.1, 5, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	5, 'CanTho 01', '101 Le Hong Phong Str.', 'cantho01@takk.com', '31526192',
	null, null, 0.1, 5, 'false')

insert into branch (
	city_id, branch_name, address, email, phone_number,
	[image], description, unit_price, commission_percent, is_deleted) 
values(
	6, 'DaNang 01', '193 Nguyen Trai Str. 1st Dist.', 'danang01@takk.com', '18251252',
	null, null, 0.05, 10, 'false')

select * from branch
go


--Account

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'admin01', null, '123123', 'La Thai An', '197 Harbourside Darling Harbour NSW 2000', 
	'1219816828', 'admin01@takk.com', 'Admin', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'manager01', null, '123123', 'Billy Sheehan', 'Victoria Dock, Hobart TAS 7000', 
	'1229916828', 'manager01@takk.com', 'Manager', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp001', 1, '123123', 'Johnny Lennon', '2301 East Lamar Blvd. Suite 140',
	'1229916000', 'emp001@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp002', 2, '123123', 'Hannah Lessey', '3 East 54th Street', 
	'1119916000', 'emp002@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp003', 3, '123123', 'Nguyen Hoang Thinh', '3480 Lakemont Blvd. Fort Mill',
	'1219916000', 'emp003@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp004', 4, '123123', 'Le Dang Khoa', '75 Ninth Avenue', 
	'1219316000', 'emp004@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp005', 5,'123123', 'Kurt Kobain', '135, Lake View Apartments',
	'1210016000', 'emp005@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address, 
	phone_number, email, role, is_blocked)
values (
	'emp006', 6, '123123', 'Thomas Blug', '153 No Trang Long Str.',
	'1019916000', 'emp006@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address,
	phone_number, email, role, is_blocked)
values (
	'emp007', 7, '123123', 'Orianthi Panagaris', '51 Forrest St Kalgoorlie WA 6430',
	'1019916000', 'emp007@takk.com', 'Employee', 'false')

insert into account (
	user_name, branch_id, password, full_name, address,
	phone_number, email, role, is_blocked)
values (
	'emp008', 7, '123123', 'Carlos Montana', '399 Queen St Brisbane QLD 4000',
	'1019916000', 'emp008@takk.com', 'Employee', 'true')

select * from account
go


--10 Customers

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'john123', 1, '123123', 'John Connor', 'Apr 22, 1979', 'Male', 
	'300 SW 10 Avenue th. Topeka', '01663493073', 'john@gmail.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'dave123', 1, '123123', 'Dave Mustaine', 'Sep 15, 1968', 'Male', 
	'1815 Kirschner Road, Kelowna', '0251922745', 'dave@gmail.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'sam123', 2, '123123', 'Sam Fisher', 'Jan 28, 1988', 'Male', 
	'299 Queen Street West. City', '09346923631', 'sam_fisher@gmail.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'lisa123', 2, '123123', 'Lisa Beth', 'Feb 21, 1962', 'Female', 
	'700 Burrard St. Vancouver', '86292812', 'lisa@gmail.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'ryu123', 3, '123123', 'Ryu Hayabusha', 'Jun 22, 1985', 'Male', 
	'14 place des Vins de France', '0958808888', 'ryu@gmail.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'chunli123', 4, '123123', 'Chun Li', 'Oct 2, 1980', 'Female', 
	'1 East Chang An Avenue', '01663493037', 'chunli@yahoo.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'steve123', 5, '123123', 'Steve Siro Vai', 'Jan 2, 1980', 'Male', 
	'7000 Westminster Hwy', '092627332', 'steve@vai.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'maruko123', 6, '123123', 'Maruko Sakura', 'May 21, 1988', 'Female', 
	'Hwy 17 & Tsawwassen Drive', '0947482745', 'maruko@yahoo.com', 'true')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'john456', 6, '123123', 'John Petrucci', 'Oct 10, 1970', 'Male', 
	'2166 Mt Newton X-Rd', '0926929993', 'petrucci@dt.com', 'false')

insert into customer (
	user_name, city_id, password, full_name, date_of_birth, gender, 
	address, phone_number, email, is_blocked)
values (
	'james123', 6, '123123', 'James Metfield', 'May 20, 1965', 'Male', 
	'74 Kedron Brook Rd Wilston QLD', '25791286', 'james@gmail.com', 'false')

select * from customer
go


--Delivery

-- From branch 1 - HCM 01

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 2,'300 SW 10 Avenue th. Topeka', 'Sam Fisher', 
	'299 Queen Street West. City', 200, 200, 20, '2010-01-21', null, 
	'2010-01-29', null, null, 'Pending', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'dave123', 1, 2,'1815 Kirschner Road, Kelowna', 'John Travolta', 
	'1 Paxton St Cleveland', 1000, 1000, 100, '2010-01-21', null, 
	'2010-02-01', null, null, 'Pending', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 3,'300 SW 10 Avenue th. Topeka', 'Ryu Hayabusha', 
	'14 place des Vins de France', 150, 525, 52.5, '2010-01-14', null, 
	'2010-01-22', null, null, 'Rejected', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'dave123', 1, 4,'1815 Kirschner Road, Kelowna', 'Chun Li', 
	'1 East Chang An Avenue', 40, 600, 60, '2010-01-20', null, 
	'2010-01-29', null, null, 'Accepted', null, 'false')

insert into delivery (	-- Canceled after acceptance but before confirming
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 5,'300 SW 10 Avenue th. Topeka', 'Steve Siro Vai', 
	'7000 Westminster Hwy', 300, 1050, 105, '2010-01-19', null, 
	'2010-01-29', null, null, 'Canceled', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 6,'300 SW 10 Avenue th. Topeka', 'Steve Siro Vai', 
	'7000 Westminster Hwy', 80, 800, 80, '2010-01-18', '2010-01-21', 
	'2010-01-28', '4408041234567893', null, 'Confirmed', null, 'false')

insert into delivery (	-- Canceled after confirming 1 day, cancellation fee = 5% totalrate
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'dave123', 1, 7,'1815 Kirschner Road, Kelowna', 'James Metfield', 
	'74 Kedron Brook Rd Wilston QLD', 100, 850, 85, '2010-01-16', '2010-01-19', 
	'2010-01-28', '4408041234567893', null, 'Canceled', 42.5, 'false')

insert into delivery (	-- PickedUp but still waiting, not delivered yet
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 3,'300 SW 10 Avenue th. Topeka', 'Tom Kruise', 
	'508 Mt Dandenong Tourist Rd Olinda', 150, 525, 52.5, '2010-01-12', '2010-01-14',
	'2010-01-20', '4408041234567893', null, 'PickedUp', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'dave123', 1, 7,'1815 Kirschner Road, Kelowna', 'Angela Pearl', 
	'299 Queen Street West. City', 200, 1700, 170, '2010-01-20', null, 
	'2010-01-29', null, null, 'Pending', null, 'false')


-- From branch 2 - HCM 02

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'sam123', 2, 3 ,'299 Queen Street West. City', 'Adam Sandler', 
	'274 Dawson Pde Keperra', 240, 960, 48, '2010-01-21', null,
	'2010-01-30', null, null, 'Pending', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'sam123', 2, 3 ,'299 Queen Street West. City', 'Lisa Beth', 
	'700 Burrard St. Vancouver', 120, 480, 24, '2010-01-10', '2010-01-13',
	'2010-01-20', '4408041234567893', null, 'PickedUp', null, 'false')

insert into delivery (	-- Canceled after accepted but before confirmed
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'sam123', 2, 5, '299 Queen Street West. City', 'Steve Siro Vai',
	'7000 Westminster Hwy', 160, 640, 32, '2009-12-20', null,
	'2010-01-01', null, null, 'Canceled', null, 'false')

insert into delivery (	-- Confirmed, waiting for pick up
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'sam123', 2, 5, '299 Queen Street West. City', 'Steve Siro Vai', 
	'7000 Westminster Hwy', 160, 640, 32, '2010-01-20', '2010-01-21',
	'2010-01-28', '4408041234567893', null, 'Confirmed', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'sam123', 2, 6,'299 Queen Street West. City', 'Jordan Ruddess', 
	'3080 Prince Edward St.', 100, 1100, 55, '2010-01-20', null,
	'2010-02-01', null, null, 'Accepted', null, 'false')


-- From branch 3 - VungTau 01

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'ryu123', 3, 1, '14 place des Vins de France', 'Steve Siro Vai', 
	'7000 Westminster Hwy', 70, 392, 39.2, '2010-01-20', null,
	'2010-01-30', null, null, 'Pending', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'ryu123', 3, 5, '14 place des Vins de France', 'Steve Siro Vai', 
	'7000 Westminster Hwy', 75, 840, 84, '2010-01-09', null,
	'2010-01-17', null, null, 'Canceled', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 3, 7,'300 SW 10 Avenue th. Topeka', 'Ryu Hayabusha', 
	'14 place des Vins de France', 80, 1536, 153.6, '2010-01-21', null, 
	'2010-01-31', null, null, 'Pending', null, 'false')


-- From branch 4 - DaLat 01

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'chunli123', 4, 2, '1 East Chang An Avenue', 'Sam Fisher',
	'299 Queen Street West. City', 40, 1024, 51.2, '2010-01-08', '2010-01-10',
	'2010-01-20', '5424000000000015', null, 'PickedUp', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'chunli123', 4, 2, '1 East Chang An Avenue', 'Sam Fisher',
	'299 Queen Street West. City', 40, 1024, 51.2, '2010-01-13', '2010-01-16',
	'2010-01-25', '5424000000000015', null, 'Confirmed', null, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'chunli123', 4, 1, '1 East Chang An Avenue', 'John Connor',
	'300 SW 10 Avenue th. Topeka', 50, 1200, 60, '2010-01-21', null,
	'2010-01-30', null, null, 'Pending', null, 'false')


-- From branch 5 - DongNai 01

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'maruko123', 5, 1,'Hwy 17 & Tsawwassen Drive','Dave Mustaine',
	'1815 Kirschner Road, Kelowna', 80, 560, 28, '2010-01-10', '2010-01-13',
	'2010-01-19', '4111111111111111', null, 'Canceled', 56, 'false')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'steve123', 5, 3, '7000 Westminster Hwy', 'Ryu Hayabusha',
	'14 place des Vins de France', 40, 560, 28, '2010-01-05', '2010-01-09', 
	'2010-01-14', '5111111111111118', null, 'PickedUp', null, 'false')

-- PickedUp deliveries for report data

insert into delivery (	-- PickedUp and finished
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 1, 7,'300 SW 10 Avenue th. Topeka', 'Jolie Foster', 
	'18 Glyde St Mosman Park', 50, 425, 42.5, '2009-12-30', '2010-01-02', 
	'2010-01-09', '4408041234567893', '2010-01-13', 'PickedUp', null, 'true')

insert into delivery (	-- PickedUp
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john123', 2, 7,'300 SW 10 Avenue th. Topeka', 'Jolie Foster', 
	'18 Glyde St Mosman Park', 50, 450, 22.5, '2009-12-28', '2009-12-30', 
	'2010-01-08', '4408041234567893', '2010-01-14', 'PickedUp', null, 'true')

insert into delivery (	-- PickedUp and finished
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'ryu123', 3, 6, '14 place des Vins de France', 'John Petrucci', 
	'2166 Mt Newton X-Rd', 70, 1512, 151.2, '2009-12-25', '2009-12-28',
	'2010-01-04', '4433335685122265', '2010-01-13', 'PickedUp', null, 'true')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'chunli123', 4, 2, '1 East Chang An Avenue', 'John Petrucci', 
	'14 place des Vins de France', 50, 1280, 64, '2010-01-01', '2010-01-03',
	'2010-01-09', '4433335685122265', '2010-01-13', 'PickedUp', null, 'true')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'ryu123', 5, 6, '14 place des Vins de France', 'John Petrucci', 
	'2166 Mt Newton X-Rd', 50, 1350, 67.5, '2010-01-01', '2010-01-03',
	'2010-01-09', '4433335685122265', '2010-01-13', 'PickedUp', null, 'true')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'john456', 6, 5, '2166 Mt Newton X-Rd', 'Tom Sanks', 
	'9 Wentworth St Parramatta', 50, 1350, 67.5, '2010-01-02', '2010-01-03',
	'2010-01-10', '4433335685122265', '2010-01-15', 'PickedUp', null, 'true')

insert into delivery (
	consigner_id, start_branch_id, destination_branch_id, pick_up_address, consignee_name, 
	consignee_address, total_weight, rate, comission, booked_date, confirmed_date, 
	pickup_date, customercc_number, delivery_date, booking_status, pubnish_rate, is_finished)
values (
	'james123', 7, 3, '74 Kedron Brook Rd Wilston QLD', 'Ryu Hayabusha', 
	'14 place des Vins de France', 90, 1080, 108, '2010-01-03', '2010-01-04',
	'2010-01-11', '4433335685122265', '2010-01-16', 'PickedUp', null, 'true')

select * from delivery
go

/*
select delivery_id as id, start_branch_id as [from], destination_branch_id as [to], consigner_id as sender, 
	tracking_code as code, booked_date as booked, confirmed_date as confirmed, pickup_date, 
	customercc_number as CC, delivery_date, booking_status, pubnish_rate, is_finished as finished 
from delivery
where booking_status='PickedUp'
go

select d.consigner_id as sender, d.start_branch_id as start, d.destination_branch_id as dest, 
	b.unit_price, b.commission_percent, d.total_weight, d.rate, d.comission, d.booking_status
from delivery d, branch b
where d.start_branch_id = b.branch_id and b.branch_id = 7
order by d.destination_branch_id asc
go

select top 5 c.full_name, c.address, ci.city_name, sum(d.rate) as totalrate 
from (customer c join city ci on c.city_id = ci.city_id) 
	join delivery d on c.[user_name] = d.consigner_id 
where d.booking_status = 'pickedup' 
	--and (d.confirmed_date between ? and ?) 
group by c.full_name, c.address, ci.city_name 
order by totalrate desc
go

select top 10 d.delivery_id, d.booked_date, c.full_name, 
	d.start_branch_id, d.destination_branch_id, d.total_weight, d.rate 
from delivery d join customer c on d.consigner_id = c.[user_name] 
where d.booking_status = 'pickedup' 
	--and (d.confirmed_date between ? and ?) 
order by rate desc
go

select top 5 b.branch_id, b.branch_name, c.city_name, sum(d.rate) as revenue, 
	sum(d.comission) as commission 
from (branch b join city c on b.city_id = c.city_id) 
	join delivery d on b.branch_id = d.start_branch_id 
where d.booking_status = 'pickedup' 
	--and (d.confirmed_date between ? and ?) 
group by b.branch_id, b.branch_name, c.city_name 
order by revenue desc
go
*/


--Packages

-- Pending deliveries
insert into package (delivery_id, weight, delivery_status, is_deleted) values (1, 100, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (1, 100, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (2, 500, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (2, 500, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (9, 200, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (10, 120, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (10, 120, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (15, 70, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (17, 100, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (17, 50, 'Planned', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (20, 50, 'Planned', 'false')


-- PickedUp, not finished

insert into package (delivery_id, weight, delivery_status, is_deleted) values (8, 50, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (8, 50, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (8, 50, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (11, 60, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (11, 60, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (18, 40, 'Waiting', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (22, 40, 'Waiting', 'false')

-- PickedUp, finished

insert into package (delivery_id, weight, delivery_status, is_deleted) values (23, 50, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (24, 50, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (25, 70, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (26, 50, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (27, 50, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (28, 50, 'Finished', 'false')
insert into package (delivery_id, weight, delivery_status, is_deleted) values (29, 50, 'Finished', 'false')


select * from package
go


--Vehicle

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('98L-7765', 1, 'Medium', 5000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('17L-8945', 1, 'Big Truck', 10000, 'Broken')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('18L-8921', 2, 'Big Truck', 10000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('56U-9924', 3,'Small Truck', 8000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('91L-4430', 3, 'Big Truck', 10000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('55U-5126', 3, 'Medium', 5000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('31L-7812', 4, 'Medium', 5000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('36U-2376', 4, 'Small Truck', 8000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('61L-1825', 5, 'Medium', 5000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('31L-2376', 5, 'Big Truck', 10000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('81U-9236', 6, 'Small Truck', 8000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status, is_deleted)
values('81U-1261', 6, 'Small Truck', 8000, 'Broken', 1)

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('31L-6102', 6, 'Medium', 5000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('10K-2512', 7, 'Big Truck', 10000, 'Free')

insert into vehicle (vehicle_id, branch_id, type, weight_limit, status)
values('69K-7212', 7, 'Medium', 5000, 'Free')

select * from vehicle
go


--Route
insert into route(from_branch_id, to_branch_id, distance) 
values(1, 2, 20)

insert into route(from_branch_id, to_branch_id, distance) 
values(2, 1, 20)

insert into route(from_branch_id, to_branch_id, distance) 
values(1, 3, 70)

insert into route(from_branch_id, to_branch_id, distance) 
values(3, 1, 70)

insert into route(from_branch_id, to_branch_id, distance) 
values(2, 3, 80)

insert into route(from_branch_id, to_branch_id, distance) 
values(3, 2, 80)

insert into route(from_branch_id, to_branch_id, distance) 
values(1, 4, 300)

insert into route(from_branch_id, to_branch_id, distance) 
values(4, 1, 300)

insert into route(from_branch_id, to_branch_id, distance) 
values(1, 5, 70)

insert into route(from_branch_id, to_branch_id, distance) 
values(5, 1, 70)

insert into route(from_branch_id, to_branch_id, distance) 
values(2, 5, 80)

insert into route(from_branch_id, to_branch_id, distance) 
values(5, 2, 80)

insert into route(from_branch_id, to_branch_id, distance) 
values(1, 6, 200)

insert into route(from_branch_id, to_branch_id, distance) 
values(6, 1, 200)

insert into route(from_branch_id, to_branch_id, distance) 
values(5, 7, 100)

insert into route(from_branch_id, to_branch_id, distance) 
values(7, 5, 100)

select * from route
go


--Trip--
--INSERT INTO TRIP(VEHICLE_ID,START_DATE,MAX_ROUTE)
--VALUES('98L-7765', getdate(),2)
--
--INSERT INTO TRIP(VEHICLE_ID,START_DATE,MAX_ROUTE)
--VALUES('56U-9924', getdate(),7)
--
--INSERT INTO TRIP(VEHICLE_ID,START_DATE,MAX_ROUTE)
--VALUES('17L-8945', getdate(),8)
--GO


--Warehouse
insert into warehouse (branch_id, package_id, is_removed) values (1, 12, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (1, 13, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (1, 14, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (2, 15, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (2, 16, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (4, 17, 'false')
insert into warehouse (branch_id, package_id, is_removed) values (5, 18, 'false')

select * from warehouse
go
